/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 80015
 Source Host           : 192.168.99.100:3306
 Source Schema         : yayi_database

 Target Server Type    : MySQL
 Target Server Version : 80015
 File Encoding         : 65001

 Date: 05/04/2019 00:43:29
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for event_form
-- ----------------------------
DROP TABLE IF EXISTS `event_form`;
CREATE TABLE `event_form`
(
    `event_id` int(11) NOT NULL,
    `form_id`  int(11) NOT NULL,
    PRIMARY KEY (`form_id`) USING BTREE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_bin;

-- ----------------------------
-- Table structure for events
-- ----------------------------
DROP TABLE IF EXISTS `events`;
CREATE TABLE `events`
(
    `event_id`               int(11)                                                       NOT NULL AUTO_INCREMENT COMMENT '主键',
    `event_title`            varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '事件标题',
    `event_type`             int(255)                                                      NOT NULL COMMENT '事件类型',
    `event_content`          text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci         NOT NULL COMMENT '事件内容(ckeditor)',
    `event_create_time`      timestamp(6)                                                  NOT NULL DEFAULT '1996-01-01 00:00:00.000000' COMMENT '事件创建时间',
    `event_dead_time`        timestamp(6)                                                  NOT NULL DEFAULT '1996-01-01 00:00:00.000000' COMMENT '事件事件结束时间',
    `event_publisher`        int(11)                                                       NOT NULL COMMENT '事件人发布人id',
    `event_admin`            int(11)                                                                DEFAULT NULL COMMENT '事件管理员id',
    `event_can_register_num` int(11)                                                       NOT NULL COMMENT '事件可报名人数',
    `status`                 int(255)                                                      NOT NULL DEFAULT '1' COMMENT '事件感兴趣人数',
    `event_like_num`         int(11)                                                                DEFAULT '0' COMMENT '感兴趣人数',
    PRIMARY KEY (`event_id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 40
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci COMMENT ='事件表';

-- ----------------------------
-- Records of events
-- ----------------------------
BEGIN;
INSERT INTO `events`
VALUES (31, '测试', 1,
        '<p>&nbsp;</p><h2 style=\"text-align:center;\">美味的托斯卡纳聚会</h2><h3 style=\"text-align:center;\">欢迎来信</h3><p>尊敬的客人，</p><p>我们很高兴欢迎您参加一年一度的<i>Flavorful托斯卡纳聚会，</i>并希望您在<a href=\"http://ckeditor.com\">Bilancino Hotel酒店</a>享受该计划以及您的住宿。</p><p>请在下面找到活动的完整时间表。</p><figure class=\"table\"><table><thead><tr><th colspan=\"2\">7月14日星期六</th></tr></thead><tbody><tr><td>上午9:30 - 上午11:30</td><td><p><strong>Americano vs. Brewed</strong> - “了解你的咖啡”：&nbsp;</p><ul><li>朱利亚比安奇</li><li>斯特凡诺加劳</li><li>朱塞佩·鲁索</li></ul></td></tr><tr><td>下午1:00 - 下午3:00</td><td><p><strong>Pappardelle al pomodoro</strong> - 现场烹饪</p><p>将最新鲜的食材 &nbsp;<br>与Rita Fresco融为一体</p></td></tr><tr><td>下午5:00 - 晚上8:00</td><td><strong>托斯卡纳葡萄园一览</strong> -&nbsp;<br>与Frederico Riscoli 品尝葡萄酒&nbsp;</td></tr></tbody></table></figure><blockquote><p>一年一度的美味托斯卡纳聚会总是一个烹饪发现。在该地区顶级酒店之一的激烈的一日住宿期间，您将获得最好的托斯卡纳风味。所有的会议都由热衷于他们职业的顶级厨师领导。我当然会建议您在日历中保存这个日期！</p><p>食品记者安吉丽娜卡尔维诺</p></blockquote><p>请到达<a href=\"http://ckeditor.com\">Bilancino Hotel酒店</a>前台<mark class=\"marker-yellow\">至少提前半小时</mark> 确保注册过程尽可能顺利进行。</p><p>我们期待着欢迎您参加此次活动。</p><p><strong>Victoria Valc</strong></p><p><strong>事件管理器</strong></p><p><strong>比兰奇诺酒店</strong></p><p>&nbsp;</p>',
        '2019-03-23 15:27:27.000000', '2019-03-23 15:27:27.000000', 1, 1, 10, 1, NULL);
INSERT INTO `events`
VALUES (36, '测试', 1,
        '<p>&nbsp;</p><p>&nbsp;</p><figure class=\"image\"><img src=\"http://118.190.157.81:8080/G0/M00/00/00/rB-WVVyYbhWAZuYnAABmpEtO0i8144.png\"></figure><p>&nbsp;</p><p>三是的撒的撒</p><p>&nbsp;</p><p>&nbsp;</p><p>&nbsp;</p><p>&nbsp;</p><p>&nbsp;</p>',
        '2019-03-25 13:58:49.000000', '2019-03-25 13:58:49.000000', 1, 1, 10, 1, NULL);
INSERT INTO `events`
VALUES (37, '测试', 1,
        '<figure class=\"image image-style-align-left\"><img src=\"http://118.190.157.81:8080/G0/M00/00/00/rB-WVVybQYeAVx5LAAHi2GK2qR005.jpeg\"></figure><p><span class=\"text-huge\"><strong>In</strong></span> today’s net-savvy world it has become common for any business to have a website which they use mostly for advertising their products and services. With the advent of search engines it has become even easier for the customers to search for the stuff online. For a website to be successful its link should land in the first three pages which the search engine brings and the rank of the page should be high which means many visitors come to the site. This can be achieved by applying search engine optimization or popularly known as SEO. This is a marketing strategy which increases the quality and quantity of traffic flow to a particular website via search engines. SEO not only affects the search engine results, but also image search, video search and industry specific vertical search engines. It determines how a search algorithm functions and searches what is popular with people. When a website link is submitted to a search engine, a spider crawls through a page to gather links which lead to other pages and stores those pages on the server of the search engine. The information collected from these pages is sent to the indexer, whose job is to extract information from those pages such as the keywords and their weights, the location of the page and other links that are stored for the spider to crawl in future. In the beginning, the search engine optimizer algorithms were dependant on the keywords, Meta tags, and index files provided by the Webmaster. Meta tags provided information about a particular page, but using them for indexing the pages didn’t prove to be successful as some Webmasters added irrelevant Meta tags to increase the number of hits and earn huge ad revenue. They even changed the HTML of the web pages to achieve a good rank for the page. But this was a case of abuse as it fetched irrelevant pages.&nbsp; Search engines then began utilizing complex ranking algorithm, which were difficult for the webmasters to manipulate so as to provide web surfers with genuine results. The rank of the web page was calculated mathematically by functions using strength and quantity of the inbound links. The higher the rank of the page the more chances it had to be viewed by a person. Later algorithms were developed which considered various other on-page factors such as rank and off-page factors such as hyperlink. Since the webmasters couldn’t manipulate the page rank, they began exchanging, selling and buying links, which lead to link spamming and even creation of numerous sites dedicated for this purpose. Algorithms became more complex by every passing day and top search engines kept their algorithms a secret. As the cost of SEO increased, advertisers were roped in to pay for it, which finally resulted in high quality web pages. Although investing in SEO is very fruitful, but at the same time is risky because with out any prior notice the algorithms being used are bound to change and the search engine will stop directing visitors to the page. Many consultants are available in the market that provides SEO services. They manipulate the HTML source code of the web site like menus, shopping carts and sometimes even the content of the website to draw more traffic. Search engines like Yahoo has algorithms that extract pages not according to the page rank but according to the cost per click or set fee, that is if a advertiser desires that the page containing his ad be displayed, he is expected to pay money for it. This is a point of controversy, as only the big businesses will be able to increase the number of hits of their page but not the small business who might be having a better quality page.<br>&nbsp;</p><h4>Google Ad Words explores ads which have words typed in the search box by the surfer. The Million Dollar Homepage started the concept of Pixel advertising, which is a graphical kind of advertising. Depending on the pixels, the space is sold to the advertiser. Keyword advertising involves advertisers who buy URLs of a site and place their ads at that location. Thus SEO is a market in its own which is yielding great results for businesses on Internet.</h4>',
        '2019-03-27 17:26:20.000000', '2019-03-27 17:26:20.000000', 1, 1, 10, 1, NULL);
INSERT INTO `events`
VALUES (38, '测试', 1, '55555', '2019-03-28 15:17:12.000000', '2019-03-28 15:17:12.000000', 1, 1, 10, 1, NULL);
INSERT INTO `events`
VALUES (39, '测试', 1, '<blockquote><p><span class=\"text-huge\"><strong>hello word！</strong></span></p></blockquote>',
        '2019-03-30 16:53:05.000000', '2019-03-30 16:53:05.000000', 1, 1, 10, 1, NULL);
COMMIT;

-- ----------------------------
-- Table structure for file
-- ----------------------------
DROP TABLE IF EXISTS `file`;
CREATE TABLE `file`
(
    `file_id`          int(11)                                                       NOT NULL AUTO_INCREMENT COMMENT '文件id',
    `file_name`        varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '文件名',
    `file_path`        varchar(255) COLLATE utf8mb4_unicode_ci                       NOT NULL COMMENT '文件地址',
    `file_creator_id`  int(11)                                                       NOT NULL COMMENT '修改人id',
    `file_size`        double                                                        NOT NULL COMMENT '文件大小',
    `file_type`        varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '文件类型',
    `file_create_time` datetime                                                      NOT NULL COMMENT '文件创建时间',
    `file_update_time` datetime                                                      NOT NULL COMMENT '文件修改时间',
    `disable`          int(11) DEFAULT '1' COMMENT '是否可用',
    PRIMARY KEY (`file_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

-- ----------------------------
-- Table structure for form
-- ----------------------------
DROP TABLE IF EXISTS `form`;
CREATE TABLE `form`
(
    `form_id`          int(11)   NOT NULL AUTO_INCREMENT COMMENT '表单id',
    `form_creator_id`  int(11)                                                DEFAULT NULL COMMENT '创建者id',
    `form_create_time` timestamp NULL                                         DEFAULT NULL COMMENT '表单创建时间',
    `form_update_time` timestamp NULL                                         DEFAULT NULL COMMENT '表单修改时间',
    `form_description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '表单描述文本',
    `form_status`      int(11)                                                DEFAULT NULL COMMENT '表单状态',
    `form_group_id`    int(11)                                                DEFAULT NULL COMMENT '表单成员组id',
    PRIMARY KEY (`form_id`) USING BTREE
) ENGINE = InnoDB
  AUTO_INCREMENT = 107
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_bin;

-- ----------------------------
-- Table structure for form_file
-- ----------------------------
DROP TABLE IF EXISTS `form_file`;
CREATE TABLE `form_file`
(
    `form_id` int(11) NOT NULL,
    `file_id` int(11) DEFAULT NULL,
    PRIMARY KEY (`form_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_bin;

-- ----------------------------
-- Table structure for group
-- ----------------------------
DROP TABLE IF EXISTS `group`;
CREATE TABLE `group`
(
    `group_id`   int(11) NOT NULL AUTO_INCREMENT,
    `group_size` int(11)  DEFAULT NULL,
    `disable`    int(255) DEFAULT '0',
    PRIMARY KEY (`group_id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 85
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_bin;

-- ----------------------------
-- Table structure for group_user
-- ----------------------------
DROP TABLE IF EXISTS `group_user`;
CREATE TABLE `group_user`
(
    `id`        int(11) NOT NULL AUTO_INCREMENT,
    `group_id`  int(11) NOT NULL,
    `user_id`   int(11) NOT NULL,
    `user_type` int(11) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 73
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_bin;

-- ----------------------------
-- Table structure for privilege
-- ----------------------------
DROP TABLE IF EXISTS `privilege`;
CREATE TABLE `privilege`
(
    `privilege_id`         int(11) NOT NULL AUTO_INCREMENT COMMENT '权限id',
    `privilege_ name`      varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '具体权限',
    `privilege_is_disable` int(255)                                                      DEFAULT NULL COMMENT '是否可用',
    PRIMARY KEY (`privilege_id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 6
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

-- ----------------------------
-- Records of privilege
-- ----------------------------
BEGIN;
INSERT INTO `privilege`
VALUES (1, 'event:create', 1);
INSERT INTO `privilege`
VALUES (2, 'event:delete', 1);
INSERT INTO `privilege`
VALUES (3, 'event:modification', 1);
INSERT INTO `privilege`
VALUES (4, 'event:read', 1);
INSERT INTO `privilege`
VALUES (5, 'user:create', 1);
COMMIT;

-- ----------------------------
-- Table structure for privilege_role
-- ----------------------------
DROP TABLE IF EXISTS `privilege_role`;
CREATE TABLE `privilege_role`
(
    `privilege_id` int(11) NOT NULL,
    `role_id`      int(11) NOT NULL,
    PRIMARY KEY (`privilege_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

-- ----------------------------
-- Table structure for role
-- ----------------------------
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role`
(
    `role_id`     int(11) NOT NULL AUTO_INCREMENT,
    `role_name`   varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `role_status` int(255)                                                      DEFAULT NULL,
    PRIMARY KEY (`role_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`
(
    `user_id`          int(11)                                          NOT NULL AUTO_INCREMENT COMMENT '主键-用户ID',
    `user_name`        varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '用户姓名',
    `user_email`       varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '用户邮箱',
    `user_pwd`         varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '用户密码',
    `user_sex`         varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '用户性别',
    `user_birthday`    date                                             DEFAULT NULL COMMENT '用户出生年月',
    `user_tell`        varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '用户联系方式',
    `user_avatar_id`   int(11)                                          DEFAULT NULL COMMENT '用户头像',
    `user_college_id`  int(11)                                          DEFAULT NULL COMMENT '用户所在学院',
    `user_role_id`     int(2)                                           NOT NULL COMMENT '用户所属角色',
    `user_create_time` datetime                                         DEFAULT NULL COMMENT '用户创建时间',
    `user_is_disable`  int(255)                                         DEFAULT NULL COMMENT '是否禁用',
    PRIMARY KEY (`user_id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 4
  DEFAULT CHARSET = utf8
  COLLATE = utf8_bin;

-- ----------------------------
-- Records of user
-- ----------------------------
BEGIN;
INSERT INTO `user`
VALUES (1, 'mano233', '2360861135@qq.com', '123456', '1', '2019-03-30', NULL, NULL, NULL, 1, NULL, NULL);
INSERT INTO `user`
VALUES (2, 'admin', '2360861135@qq.com', 'admin', NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL);
INSERT INTO `user`
VALUES (3, 'super', '2360861135@qq.com', '123456', '1', '1996-06-10', '17718263748', NULL, NULL, 1, NULL, NULL);
COMMIT;

-- ----------------------------
-- Table structure for user_role
-- ----------------------------
DROP TABLE IF EXISTS `user_role`;
CREATE TABLE `user_role`
(
    `user_id` int(11) NOT NULL,
    `role_id` int(11) NOT NULL,
    PRIMARY KEY (`user_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

-- ----------------------------
-- Procedure structure for createForm
-- ----------------------------
DROP PROCEDURE IF EXISTS `createForm`;
delimiter ;;
CREATE PROCEDURE `yayi_database`.`createForm`(IN eventId INT, IN groupSize INT, IN userId INT,
                                              IN formCreateTime TIMESTAMP)
BEGIN
    DECLARE
        insertGroupId,
        insertFormId INT DEFAULT 0;
    START TRANSACTION;
    INSERT INTO `group` (group_size)
    VALUES (groupSize);

    SET insertGroupId = LAST_INSERT_ID();
    INSERT INTO group_user (group_id, user_id, user_type)
    VALUES (insertGroupId, userId, 1);
    INSERT INTO form (form_create_time, form_update_time, form_group_id, form_creator_id)
    VALUES (formCreateTime, formCreateTime, insertGroupId, userId);

    SET insertFormId = LAST_INSERT_ID();
    INSERT INTO event_form (event_id, form_id)
    VALUES (eventId, insertFormId);
    COMMIT;
    SELECT `user`.user_id,
           `user`.user_name,
           `events`.event_id,
           `events`.event_title,
           form.form_id,
           form.form_group_id,
           form_create_time,
           form_update_time,
           form_description
    FROM `events`
             LEFT JOIN event_form ON event_form.event_id = `events`.event_id
             LEFT JOIN form ON form.form_id = event_form.form_id
             LEFT JOIN `group` ON `group`.group_id = form.form_group_id
             LEFT JOIN group_user ON group_user.group_id = `group`.group_id
             LEFT JOIN `user` ON group_user.user_id = `user`.user_id
    WHERE `events`.event_id = eventId
      AND `user`.user_id = userId;

END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for deleteForm
-- ----------------------------
DROP PROCEDURE IF EXISTS `deleteForm`;
delimiter ;;
CREATE PROCEDURE `yayi_database`.`deleteForm`(IN formId INT)
BEGIN
    DECLARE groupId INT DEFAULT 0;
    START TRANSACTION;
    SELECT form_group_id INTO groupId FROM form WHERE form_id = formId;
    DELETE FROM `group` WHERE `group`.group_id = groupId;
    DELETE FROM form_file WHERE form_file.form_id = formId;
    DELETE FROM event_form WHERE event_form.form_id = formId;
    DELETE FROM group_user WHERE group_user.group_id = groupId;
    DELETE FROM form WHERE form.form_id = formId;
    COMMIT;
END
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;
